package com.utils;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.*;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
@Slf4j
public class ExcelUtils {


        public static void main(String[] args) {
            File file = new File("C:\\Users\\Administrator.DESKTOP-GTF5LQT\\Desktop\\临时文件\\呼叫中心\\PRD Set up Automatic Process to transfer data to DELL GC Commercial 1.3.xlsx");
            // File file = new File("E:/2.xls");
            try {
                List<List> list = importExcel(file.getPath());
                System.out.println();
                System.out.println("---------------------分隔---------------------");
                System.out.println();
                System.out.println(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


        public static List<List> importExcel(String filePath) throws Exception {
            // 读取上传文件(excel)的名字，含后缀后
            File files = new File(filePath);
            for (File file : files.listFiles()) {
                String fileName = file.getName();

                Workbook wb = null;
                // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
                Iterator<Sheet> sheets = null;
                List<List> returnlist = new ArrayList<>();
                try {
                    if (fileName.endsWith("xls")) {
                        wb = new HSSFWorkbook(new FileInputStream(file));
                        sheets = wb.iterator();
                    } else if (fileName.endsWith("xlsx")) {
                        wb = new XSSFWorkbook(new FileInputStream(file));
                        sheets = wb.iterator();
                    }
                    if (sheets == null) {
                        continue;
                    }
                    // 遍历excel里每个sheet的数据。
                    while (sheets.hasNext()) {
                        System.out.println("-----遍历sheet-----");
                        Sheet sheet = sheets.next();
                        List<Map> list = getCellValue(sheet);
                        System.out.println(list);
                        returnlist.add(list);
                    }
                } catch (Exception ex) {
                    throw ex;
                } finally {
                    if (wb != null) {
                        wb.close();
                    }
                }
            }
            return null;
        }


        // 获取每一个Sheet工作表中的数。
        private static List<Map> getCellValue(Sheet sheet) {
            String dburl="jdbc:mysql://localhost:3228/FRD?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true";
            String dbUserName="root";
            String psd="Sinobase@123";

            final String sheetName = sheet.getSheetName();
            final String tableName = sheetName.split("gmdb_")[1].replaceAll("_"," ");
            final List<String> columnList = MysqlDbUtil.getColumnList(dburl, dbUserName, psd, tableName);
            List<Map<String,Object>> returnInfo=new ArrayList<>();
            if("Feed Inventory".equals(sheetName)){
                return  null;
            }
            log.info(sheetName+"-------");
            List<Map> list = new ArrayList<>();
            // sheet.getPhysicalNumberOfRows():获取的是物理行数，也就是不包括那些空行（隔行）的情况
            for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
                Map map = new HashMap<>();
                // 获得第i行对象
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                } else {
                    // getLastCellNum：获取列数(比最后一列列标大1)
                    // 通常导入的excel是符合需求规范的，这里因为我们准备的excel列数是7列，如果小于7列说明传入的excel文件有问题。

                    int j = row.getFirstCellNum();// 获取第i行第一个单元格的下标
                    for (int k = j; true ; k++) {
                        Cell cell2 = row.getCell(k);
                        Object obj = null;
                        if (cell2 != null) { // getCell 获取单元格数据
                            switch (cell2.getCellType()) {
                                case BOOLEAN:
                                    obj = cell2.getBooleanCellValue();
                                    break;
                                case NUMERIC:
                                    Double numericCellValue = cell2.getNumericCellValue();
                                    obj = numericCellValue.intValue();
                                    break;
                                case STRING:
                                    obj = cell2.getStringCellValue();
                                    break;
                                case BLANK:
                                    break;
                                case ERROR:
                                    obj = cell2.getErrorCellValue();
                                    break;
                            }
                        } else {
                            break;
                        }
                        log.info(String.valueOf(obj));

                    }
                }


            }
            return null;
        }
}
